package com.decent.ejfadmin.report.dao.provider;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.common.bean.SupplyCard;
import com.decent.ejfadmin.utils.SQLWrapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author weicx
 * @date 2018/08/01
 */
public class CollectCardUserReportDaoProvider {
    /**
     * 分页查询收卡会员统计计数
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardUserReportCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT count(1) FROM (SELECT 1 FROM (SELECT ");
        generateSql(sql, params);
        sql.append(" GROUP BY t1.userId, t1.startDate ");
        SupplyCard card = (SupplyCard) params.get("card");
        if (StringUtils.isNotBlank(card.getArea())) {
            sql.append(" ,t1.area ");
        }
        sql.append(") t2 ");
        return sql.toString();
    }

    /**
     * 分页查询收卡会员统计
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardUserReportPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        SupplyCard card = (SupplyCard) params.get("card");
        boolean noArea = StringUtils.isBlank(card.getArea());
        sql.append("SELECT u.account userAccount,\n" +
                "i.name userName,t1.startDate,t1.userId, ");
        sql.append(noArea ? "'全部'" : "t1.area").append(" area,");
        sql.append("  count(1)                                          AS total,\n" +
                "  sum(if(t1.productType >310, t1.settlePrice, t1.face))                                      AS face,\n" +
                "  sum(if (t1.status=1,t1.settlePrice,0) ) AS settlePrice,\n" +
                "  sum(if(t1.productType = 1, t1.face, 0))           AS ydFace,\n" +
                "  sum(if(t1.productType = 1, t1.settlePrice, 0))    AS ydSettle,\n" +
                "  sum(if(t1.productType = 2, t1.face, 0))           AS ltFace,\n" +
                "  sum(if(t1.productType = 2, t1.settlePrice, 0))    AS ltSettle,\n" +
                "  sum(if(t1.productType = 3, t1.face, 0))           AS dxFace,\n" +
                "  sum(if(t1.productType = 3, t1.settlePrice, 0))    AS dxSettle,\n" +
                "  sum(if(t1.productType = 300 or t1.productType =302, t1.face, 0))        AS sinopecFace,\n" +
                "  sum(if(t1.productType = 300 or t1.productType =302, t1.settlePrice, 0)) AS sinopecSettle,\n" +
                "  sum(if(t1.productType = 310, t1.face, 0))        AS cnpcFace,\n" +
                "  sum(if(t1.productType = 310, t1.settlePrice, 0)) AS cnpcSettle,\n" +
                "  sum(if(t1.productType = 20, t1.face, 0))          AS wnFace,\n" +
                "  sum(if(t1.productType = 20, t1.settlePrice, 0))   AS wnSettle,\n" +
                "  sum(if(t1.productType = 16, t1.face, 0))          AS dxtFace,\n" +
                "  sum(if(t1.productType = 16, t1.settlePrice, 0))   AS dxtSettle,\n" +
                "  sum(if(t1.productType = 15, t1.face, 0))          AS ytFace,\n" +
                "  sum(if(t1.productType = 15, t1.settlePrice, 0))   AS ytSettle,\n" +
                " sum(if(t1.productType >310, t1.settlePrice, 0))          AS qyFace,\n" +
                "  sum(if(t1.productType >310 AND t1.`status`=1, t1.userPrice, 0))   AS qySettle\n" +
                "FROM (SELECT sc.face,sc.settlePrice,sc.userPrice,sc.status,sc.productType,");
        generateSql(sql, params);

        sql.append(" GROUP BY t1.userId,t1.startDate ");
        if (!noArea) {
            sql.append(" ,t1.area ");
        }
        Page page = (Page) params.get("pageUnit");
        if (page == null) {
            return sql.toString();
        }
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 分页查询收卡会员统计组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateSql(StringBuffer sql, HashMap<String, Object> params) {
        sql.append(" sc.userId,sc.startDate,IF(sc.supplyArea = 0, '全国', sc.area) area FROM yuhuang_supply_card sc " +
                "WHERE sc.startTime >= #{startTime} AND sc.startTime < #{endTime} ");
        //销卡地区
        SupplyCard card = (SupplyCard) params.get("card");
        if (StringUtils.isNotBlank(card.getArea())) {
            if (StringUtils.isNumeric(card.getArea())) {
                sql.append(" and sc.supplyArea = #{card.area} ");
            } else {
                sql.append(" and sc.supplyArea = 1 and sc.area = #{card.area} ");
            }
        }
        sql.append(") t1,\n" +
                "yuhuang_sup_user u,\n" +
                "yuhuang_sup_info i WHERE t1.userId=u.user_id and u.user_id=i.user_id ");
        //会员
        if(StringUtils.isNotBlank((String)params.get("userAccount"))){
            sql.append(" and (instr(u.account,#{userAccount})>0 or instr(i.name,#{userAccount})>0) ");
        }
    }
}
